﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel.DataAnnotations;
using System.Data.SqlClient;
using System.Data;

namespace EAD_CA3.Models
{
    public partial class FixtureData
    {
        // List used to populate list of filter choices
        public static List<String> FilterList
        {
            get
            {
                return new List<String> { "Specify a County and retrieve their Division", 
                    "Specify a Division and retrieve all Counties in that Division", 
                    "Specify a Division and retrieve all Fixtures in that Division", 
                    "Find all Home and Away Fixtures for a specified County", 
                    "Find all Home Fixtures for a specified County", 
                    "Find all Away Fixtures for a specified County", 
                    "Find all Fixtures on a Specified Date"};
            }
        }

        // List used to populate Counties drop down box, accessed from the database
        public static List<String> CountyList
        {
            get
            {
                EADMiniProjectEntities ead = new EADMiniProjectEntities();
                var query = ( from c in ead.CountyDivisions
                              orderby c.County
                              select c.County);
                return query.ToList();
            }
        }

        // List used to populate Division drop down box, accessed from the database
        public static List<String> DivisionList
        {
            get
            {
                EADMiniProjectEntities ead = new EADMiniProjectEntities();
                var query = ( from c in ead.CountyDivisions
                            select c.League_Division).Distinct();
                return query.ToList();
            }
        }

        // List used to populate Fixture Date drop down box, accessed from the database
        public static List<DateTime> FixtureDateList
        {
            get
            {
                EADMiniProjectEntities ead = new EADMiniProjectEntities();
                var query = ( from c in ead.FixtureDatas
                            select c.Fixture_Date).Distinct();
                return query.ToList();
            }
        }

        // Specify a county and get their division back
        public List<CountyDivision> QueryTeamDivision(string teamName)
        {
            EADMiniProjectEntities ead = new EADMiniProjectEntities();
            var queryTeamDivision = ead.CountyDivisions.Where(t => t.County == teamName);
            return queryTeamDivision.ToList();
        }

        // Specify a division and get all the teams in it
        public List<CountyDivision> QueryAllTeamsInADivision(string division)
        {
            EADMiniProjectEntities ead = new EADMiniProjectEntities();
            var allTeamsInDivision = ead.CountyDivisions.Where(t => t.League_Division == division);
            return allTeamsInDivision.ToList();
        }

        // Specify a division and get all the fixutures in that division
        public List<FixtureData> QueryFixturesByDivision(string division)
        {
            EADMiniProjectEntities ead = new EADMiniProjectEntities();
            var queryDivisionFixtures = ead.FixtureDatas.Where(t => t.Division == division);
            return queryDivisionFixtures.ToList();
        }

        // Find all the home and away fixtures for a specified team
        public List<FixtureData> QueryFixturesByTeam(string teamName)
        {
            EADMiniProjectEntities ead = new EADMiniProjectEntities();
            var queryDivisionFixtures = ead.FixtureDatas.Where(t => t.Home_Team == teamName || t.Away_Team == teamName);
            return queryDivisionFixtures.ToList();
        }

        // Find all the home and away fixtures for a specified team
        public List<FixtureData> QueryFixturesByDate(DateTime date)
        {
            EADMiniProjectEntities ead = new EADMiniProjectEntities();
            var queryDivisionFixtures = ead.FixtureDatas.Where(t => t.Fixture_Date == date);
            return queryDivisionFixtures.ToList();
        }

        // Get the home fixtures for a specified team
        public List<FixtureData> QueryFixturesByHomeTeam(string teamName)
        {
            EADMiniProjectEntities ead = new EADMiniProjectEntities();
            var queryDivisionFixtures = ead.FixtureDatas.Where(t => t.Home_Team == teamName).OrderBy(t => t.Home_Team);
            return queryDivisionFixtures.ToList();
        }

        // Get the away fixtures for a specified team
        public List<FixtureData> QueryFixturesByAwayTeam(string teamName)
        {
            EADMiniProjectEntities ead = new EADMiniProjectEntities();
            var queryDivisionFixtures = ead.FixtureDatas.Where(t => t.Away_Team == teamName).OrderBy(t => t.Away_Team);
            return queryDivisionFixtures.ToList();
        }
    }
}